package edu.tyut.game.dao.poem;

import edu.tyut.game.model.PoemPoet;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface PoemDao {
    @Select("select id from tb_poem where content like CONCAT('%',#{content},'%')")
    List<Integer> getPoemId(String content);

    @Select("select distinct REPLACE(dynasty, '\\'', '') as dynasty from tb_poet")
    List<String> getDynasties();


    @Select("select poem.id as poemId, poet.id as poetId, poem.title, poem.content, REPLACE(poet.dynasty, '\\'', '') as dynasty, REPLACE(poet.name, '\\'', '') as poet_name, REPLACE(poet.info, '\\'', '') as poet_info " +
            "from tb_poem as poem, tb_poet as poet where dynasty = CONCAT('\\'', #{dynasty}, '\\'') and poem.poet_id = poet.id")
    @Results({
            @Result(property = "poetName", column = "poet_name"),
            @Result(property = "poetInfo", column = "poet_info"),
    })
    List<PoemPoet> getPoemListByDynasty(String dynasty);

    @Select("select poem.id as poemId, poet.id as poetId, poem.title as title, poem.content as content, REPLACE(poet.dynasty, '\\'', '') as dynasty, REPLACE(poet.name, '\\'', '') as poet_name, REPLACE(poet.info, '\\'', '') as poet_info " +
            "from tb_poem as poem, tb_poet as poet where content like CONCAT('%', #{content}, '%') and poem.poet_id = poet.id")
    @Results({
            @Result(property = "poetName", column = "poet_name"),
            @Result(property = "poetInfo", column = "poet_info"),
    })
    List<PoemPoet> getPoemListBySearch(String content);

    // 诗词推荐
    @Select("select poem_id, poet_id, title, content, REPLACE(dynasty, '\\'', '') as dynasty, REPLACE(tb_poet.name, '\\'', '') as poet_name, tb_poet.info as poet_info, sum(click_count) as sum " +
            "from tb_poem , tb_poem_recommend, tb_poet where tb_poem.id = tb_poem_recommend.poem_id and tb_poem.poet_id = tb_poet.id group by poem_id order by sum desc")
    @Results({
            @Result(property = "poemId", column = "poem_id"),
            @Result(property = "poetId", column = "poet_id"),
            @Result(property = "poetName", column = "poet_name"),
            @Result(property = "poetInfo", column = "poet_info")
    })
    List<PoemPoet> getPoemOrderByClickCount();
}
